package com.amado.oracle.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.amado.oracle.dao.EmpDao;
import com.amado.oracle.entity.Emp;
import com.amado.oracle.util.DBConnection;

import oracle.jdbc.OracleTypes;

public class EmpDaoImpl implements EmpDao {

	@Override
	public void add(Emp emp) {
		String procedure = "{call proc_emp_insert(?,?,?)}";
		Connection connection = DBConnection.getConn();
		CallableStatement cstmt;
		try {
			cstmt = connection.prepareCall(procedure);
			cstmt.setString(1, emp.getEmpNo());
			cstmt.setString(2, emp.getEmpName());
			cstmt.setInt(3, emp.getDeptNo());
			cstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public void upadte(Emp emp) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(String empNo) {
		// TODO Auto-generated method stub

	}

	@Override
	public void list() {
		String procedure = "{call proc_emp_select(?)}";
		Connection connection = DBConnection.getConn();
		CallableStatement cstmt;
		try {
			cstmt = connection.prepareCall(procedure);
			//出参是一个Oracle游标
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);// oracle驱动包里的类 import oracle.jdbc.OracleTypes;
			cstmt.execute();
			ResultSet rs = (ResultSet) cstmt.getObject(1);
			while (rs.next()) {
				/**
				 * create or replace procedure proc_emp_select(CUR_EMP out SYS_REFCURSOR) is
				 *	begin
				 *	  open CUR_EMP for
				 *	    select id, emp_no, emp_name, dept_no, create_time, update_time
				 *	      FROM T_EMP;
				 *	END;
				 */
				Long id = rs.getLong(1);
				String empName = rs.getString(2);
				System.out.println(id + "  " + empName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
